package github.sf.fw.tools.db.impl;


import github.sf.fw.check.exception.ExecuteDDLException;
import github.sf.fw.tools.db.AbstractTableDataWriter;
import github.sf.fw.tools.db.DSConnector;
import github.sf.fw.tools.db.DSProperty;
import github.sf.fw.tools.db.connector.JdbcConnector;
import github.sf.fw.tools.db.impl.mysql.MysqlConstants;
import github.sf.fw.tools.db.model.ColumnInfo;
import github.sf.fw.tools.db.model.ResultData;
import github.sf.fw.tools.db.model.ResultRow;
import github.sf.fw.tools.db.model.TableInfo;
import github.sf.fw.utils.StrUtil;
import org.springframework.util.CollectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public abstract class RdbmsTableDataWriter extends AbstractTableDataWriter {


    public RdbmsTableDataWriter(DSProperty dsProperty) {
        super(dsProperty);
    }

    /**
     * 写入数据
     */
    @Override
    public int writer(TableInfo tableInfo, List<ResultRow> resultRowList) {
        JdbcConnector jdbcConnector = null;
        int index = 0;
        try {
            jdbcConnector = new JdbcConnector(dsProperty);
            writer(jdbcConnector, tableInfo, resultRowList);
        } finally {
            if (jdbcConnector != null) {
                jdbcConnector.close();
            }
        }
        return index;
    }

    /**
     * 将数据写入数据库
     *
     * @param dsConnector   数据库连接
     * @param tableInfo     表格信息（目前里面只用了TableName）
     * @param resultRowList 数据
     */
    @Override
    public void writer(DSConnector dsConnector, TableInfo tableInfo, List<ResultRow> resultRowList) {
        JdbcConnector jdbcConnector = (JdbcConnector) dsConnector;
        int index = 0;
        int offset = 0;
        try {
            int len = resultRowList.size();
            ResultRow firstRow = resultRowList.get(0);
            StringBuilder insertPrefix = getInsertPrefix(tableInfo.getName(), firstRow);
            while (index < len) {
                StringBuilder sql = new StringBuilder(insertPrefix);
                for (int i = 0; i < batchSize; i++) {
                    if (index == len) {
                        break;
                    }
                    ResultRow resultRow = resultRowList.get(index);
                    sql.append(" (");
                    for (int i1 = 0; i1 < resultRow.size(); i1++) {
                        ResultData data = resultRow.get(i1);
                        if (i1 == resultRow.size() - 1) {
                            sql.append(data.getColumnValue());
                            break;
                        }
                        sql.append(data.getColumnValue()).append(",");
                    }
                    sql.append(") ");
                    index++;
                }
                sql.append(";");
                jdbcConnector.doUpdate(sql.toString(), null);
                offset++;
            }
        } catch (SQLException e) {
            throw new RuntimeException(StrUtil.format("offset is {}", offset), e);
        }
    }

    @Override
    public void batchWrite(Object connObj, TableInfo tableInfo, List<ResultRow> resultRows) {
        Connection conn = (Connection) connObj;
        try {
            if (tableInfo.getTableExtraInfo().getExecuteTableDDL()) {
                Statement statement = conn.createStatement();
                String s = createDDL(tableInfo, tableInfo.getTableExtraInfo().getTargetTableName());
                log.debug("建表语句\n：{}", s);
                statement.executeUpdate(s);
                statement.close();
                tableInfo.getTableExtraInfo().setExecuteTableDDL(false);
            }
            //如果表数据为空，则不导入
            if (CollectionUtils.isEmpty(resultRows)) {
                return;
            }
            String sql = prepareSql(tableInfo.getTableExtraInfo().getTargetTableName(), resultRows.get(0)).toString();
            PreparedStatement ps = conn.prepareStatement(sql);
            for (ResultRow resultRow : resultRows) {
                for (int i = 0; i < resultRow.size(); i++) {
                    ps.setObject(i + 1, resultRow.get(i).getColumnValue());
                }
                ps.addBatch();
            }
            ps.executeBatch();
            ps.close();
        } catch (SQLException e) {
            try {
                conn.close();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }
    }

    @Override
    public void doUpdate(String script, Object[] args) {
        JdbcConnector jdbcConnector = null;
        int index = 0;
        try {
            jdbcConnector = new JdbcConnector(dsProperty);
            jdbcConnector.doUpdate(script, args);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (jdbcConnector != null) {
                jdbcConnector.close();
            }
        }
    }

    private StringBuilder prepareSql(String tableName, ResultRow resultRow) {
        StringBuilder sql = buildInsert(tableName, resultRow);
        sql.append(") values (");
        for (ResultData data : resultRow) {
            sql.append("?,");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")");
        return sql;
    }

    private StringBuilder buildInsert(String tableName, ResultRow resultRow) {
        StringBuilder sql = new StringBuilder("insert into ");
        sql.append(tableName).append("(");
        for (ResultData column : resultRow) {
            sql.append("`").append(column.getColumnName()).append("`").append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        return sql;
    }

    private StringBuilder getInsertPrefix(String tableName, ResultRow resultRow) {
        StringBuilder sql = buildInsert(tableName, resultRow);
        sql.append(") values ");
        return sql;
    }

    public String createDDL(TableInfo tableInfo, String tableName) {

        List<ColumnInfo> columnInfoList = tableInfo.getColumnInfoList();
        if (CollectionUtils.isEmpty(columnInfoList)) {
            throw new IllegalArgumentException("列信息为空，不能生成表创建语句:" + tableName);
        }
        //doris的建表语句
//        StringBuilder sb = new StringBuilder("DROP TABLE IF EXISTS `" + tableName + "`;\ncreate table ");
        StringBuilder sb = new StringBuilder("CREATE TABLE IF NOT EXISTS `" + tableName + "` ");
//        sb.append(tableName);
        sb.append("(\n");
//        List<String> primaryKeys = new ArrayList<>();
//        List<String> allKeys = new ArrayList<>();
        for (ColumnInfo columnInfo : columnInfoList) {
            sb.append("`").append(columnInfo.getName()).append("`").append(" ").append(columnInfo.getType());
            if ("PRI".equals(columnInfo.getColumnKey())) {
//                primaryKeys.add(columnInfo.getName());
                sb.append(" ").append(MysqlConstants.PRIMARY_KEY);
            }
//            allKeys.add(columnInfo.getName());
            sb.append(",\n");
        }
        sb.deleteCharAt(sb.length() - 2);
        sb.append(")");
        return sb.toString();
    }

    @Override
    public boolean createTable(String ddl) {
        return executeDDL(ddl);
    }

    protected boolean executeDDL(String ddl) {
        try (JdbcConnector jdbcConnector = new JdbcConnector(dsProperty); Connection connection = jdbcConnector.getConn()) {
            connection.setAutoCommit(true);
            PreparedStatement ps = connection.prepareStatement(ddl);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            ExecuteDDLException.throwException(StrUtil.format("运行ddl错误：{}", ddl), e);
        }
        return true;
    }
}
